package com.cym.utils;

import cn.hutool.core.util.StrUtil;

import java.util.ArrayList;
import java.util.List;

/**
 * 乐云 建表语句生成
 */
public class LyChanger {
    public static void main(String[] args) {
//        String s = "id,trans_id,corp_id,corp_name,usc_code,corp_type,work_code,work_name,regist_address,area_code,area_name,be_accept_org_id,be_accept_org,verify_code,create_date,enter_date,license_code,old_license_code,license_state,issue_first_date,issue_date,valid_until,issue_org,signer,supervise_org,supervise_person,data_state,data_sources,license_join_id,\"LEGAL_PERSON_ID\",\"LEGAL_PERSON\",\"LEGAL_PERSON_PHONE\",\"LEGAL_PERSON_CARD_TYPE\",\"LEGAL_PERSON_CARD_NO\",\"CONTACTS\",\"CONTACT_TEL\",\"FAX\",\"MAIL\",\"POSTAL_CODE\",\"MISS_CONTENT\",\"MISS_REASON\",\"MISS_DATE\",\"PUBLISH_MEDIA\",\"PUBLISH_DATE\",\"QR_CODE\",\"APPLY_REASON\",\"PERMIT_SITUATIONS\",\"REGISTER_SITUATIONS\",\"REMARKS\",\"OPERATION_AREA_ID\",\"OPERATION_AREA_NAME\",\"OPERATION_VILLAGE\",\"COPY_COUNT\",\"VALID_YEAR\",\"REMARK\",\"STATUS\",\"DECIDE_DATE\",\"LICENSE_ITEMS\",\"LICENCE_STATUS\",\"CREATEAUTHOR_ID\",\"CREATEAUTHOR\",\"CREATEDATE\",\"UPDATEAUTHOR\",\"UPDATEDATE\",\"DATA_STATUS\",\"QUALITY_PRINCIPAL_NAME\",\"CORP_PRINCIPAL_NAME\",\"QUAL_PERSON_ID\",\"CORP_PRINCIPAL_PERSON_ID\",\"AIC_REGISTER_ORG\",\"OTHER_MEMO\",\"ITEM_ALL_INVEST\",\"YEAR_PRODUCE_ABILITY\",\"OLD_LICENSE_NO\",\"OLD_LICENSE_VALID_TO\",\"OLD_LICENSE_ITEMS\",\"OLD_HEALTH_LICENSE_NO\",\"OLD_HEALTH_LICENSE_ITEMS\",\"OLD_HEALTH_LICENSE_VALID_TO\",\"OLD_IND_PRODUCE_LICENSE_NO\",\"OLD_IND_PRODUCE_LICENSE_ITEMS\",\"OLD_IND_PRODUCE_LICENSE_VALID\",\"FILL_REASON\",\"LICENSE_LOSS\",\"NOTICE_MEDIA_NAME\",\"NOTICE_DATE\",\"LOGOUT_REASON\",\"CORPPRINCIPALLINKTEL\",\"CK_AREA_ID\",\"CK_AREA_NAME\",\"IF_CK_ADDRESS\",\"CK_ADDRESS\",\"WORKER_NUM\",\"PRODUCTION_PEOPLE_NUM\",\"QUAL_GLR_NUM\",\"SURVEYOR_NUM\",\"CONSTRUCTION_AREA\",\"CLEAN_AREA\",\"WAREHOUSE_AREA\",\"ENTERPRISE_TYPE\",\"OPEN_AREA_NAME\",\"OUTPUT_VALUE\",\"V001\",\"V002\",\"V003\",\"V004\",\"V005\",\"V006\",\"V007\",\"V008\",v009,v010,v011,v012,v013,v014,v015,d001,d002,d003,d004,d005,create_time,update_time";
//        String replace = s.replace("\"", "");
//        System.out.println(replace);
//         sql = sql.replace("ods_ly_recruit_info", "ods_recruit_info").replace("NOT NULL", "");
        String ods_patent = test2(sql, "ods_recruit_info");
        System.out.println(ods_patent);
    }


    public static String test2(String createSql, String table) {
        String lyTable = table.replace("ods_", "ods_ly_");
        createSql = createSql.replace(lyTable, table).replace("NOT NULL", "");
        List<String> list = new ArrayList<>();
        createSql = createSql.replace("WITH (\n" +
                "\tchecksum=on\n" +
                ")", "");
        createSql = createSql.replace("ly_", "");
        String[] lines = createSql.split("\n");
        List<String> results = new ArrayList<String>();

        for (int i = 0; i < lines.length; i++) {
            String line = lines[i].trim();
            if (line.contains("CONSTRAINT")) {
                if (!results.contains("col_date_lt DATE DEFAULT CURRENT_DATE")) {
                    results.add("col_date_lt DATE DEFAULT CURRENT_DATE");
                }
                // 跳过这一行
                continue;
            }
            if (line.contains("CREATE INDEX")) {
                // 跳过这一行
                continue;
            }
            if (line.equals(")"))
                line += "distribute by shard(_id)to group default_group";
            results.add(line);
        }
        String join = StrUtil.join("\n", results);
//        System.out.println();
        list.add("\\c ods;");
        list.add(join);
        list.add("CREATE TABLE IF NOT EXISTS ods_ly." + table + "_tmp (LIKE ods_ly." + table + ");");
        list.add("ALTER TABLE ods_ly." + table + " RENAME COLUMN XX TO XX_encode;");
        list.add("ALTER TABLE ods_ly." + table + " ALTER COLUMN XX_encode TYPE VARCHAR(600);");
        list.add("\\c stg;");
        String stg = join.replace("ods_", "stg_");
        list.add(stg);
        String stgTableName = table.replace("ods", "stg");
        list.add("CREATE TABLE IF NOT EXISTS stg_ly." + stgTableName + "_tmp1 (LIKE stg_ly." + stgTableName + ");");
        list.add("truncate table ods_ly." + table +"_tmp;");
        list.add("truncate table stg_ly." + stgTableName + "_tmp1;");
        list.add("delete from ods_ly."+ table +" where col_date_lt =current_date");
        return StrUtil.join("\n", list);
    }

    public static String sql = "\n" +
            "CREATE TABLE ods_ly.ods_ly_recruit_info (\n" +
            "\t\"_id\" varchar(256) NOT NULL, -- 招聘信息ID\n" +
            "\tcompany_id varchar(256) NULL DEFAULT NULL::character varying, -- 企业Id,外键\n" +
            "\tcompany_name varchar(256) NULL DEFAULT NULL::character varying, -- 公司名称\n" +
            "\t\"name\" varchar(256) NULL DEFAULT NULL::character varying, -- 职位名称\n" +
            "\twork_area varchar(256) NULL DEFAULT NULL::character varying, -- 工作地点\n" +
            "\tmin_salary numeric(22, 6) NULL DEFAULT NULL::numeric, -- 最小工资\n" +
            "\tmax_salary numeric(22, 6) NULL DEFAULT NULL::numeric, -- 最大工资\n" +
            "\tmin_work_years numeric(22, 6) NULL DEFAULT NULL::numeric, -- 最小工作年限\n" +
            "\tmax_work_years numeric(22, 6) NULL DEFAULT NULL::numeric, -- 最大工作年限\n" +
            "\teducation varchar(256) NULL DEFAULT NULL::character varying, -- 学历\n" +
            "\trecruit_numbers int4 NULL, -- 招聘人数\n" +
            "\tpublish_date int8 NULL, -- 发布时间\n" +
            "\tworkplace text NULL, -- 上班地址\n" +
            "\tfunction_type varchar(256) NULL DEFAULT NULL::character varying, -- 职能类别\n" +
            "\tkeyword varchar(256) NULL DEFAULT NULL::character varying, -- 关键字\n" +
            "\tjob_info text NULL, -- 职位信息\n" +
            "\tupdate_time int8 NULL, -- 招聘更新时间\n" +
            "\tprovince varchar(256) NULL DEFAULT NULL::character varying, -- 省份\n" +
            "\tcity varchar(256) NULL DEFAULT NULL::character varying, -- 城市\n" +
            "\tcome_from varchar(256) NULL DEFAULT NULL::character varying, -- 来源网站\n" +
            "\turl varchar(256) NULL DEFAULT NULL::character varying, -- 来源网址\n" +
            "\tjob_type int4 NULL, -- 职位类型\n" +
            "\tjob_msg text NULL, -- 岗位福利等信息\n" +
            "\tjob_welfare varchar(256) NULL DEFAULT NULL::character varying, -- 岗位福利\n" +
            "\tmain_points varchar(256) NULL DEFAULT NULL::character varying, -- 招聘要点\n" +
            "\tupdate_at int8 NULL, -- 更新时间\n" +
            "\tmin_id varchar(256) NULL DEFAULT NULL::character varying, -- 最小ID\n" +
            "\tCONSTRAINT ods_ly_recruit_info_pkey PRIMARY KEY (_id)\n" +
            ")\n" +
            "WITH (\n" +
            "\tchecksum=on\n" +
            ");\n" +
            "CREATE INDEX idx_ods_ly_recruit_info_company_id ON ods_ly.ods_ly_recruit_info USING btree (company_id) WITH (checksum='on');\n" +
            "COMMENT ON TABLE ods_ly.ods_ly_recruit_info IS '招聘信息';\n" +
            "\n" +
            "-- Column comments\n" +
            "\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.\"_id\" IS '招聘信息ID';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.company_id IS '企业Id,外键';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.company_name IS '公司名称';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.\"name\" IS '职位名称';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.work_area IS '工作地点';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.min_salary IS '最小工资';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.max_salary IS '最大工资';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.min_work_years IS '最小工作年限';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.max_work_years IS '最大工作年限';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.education IS '学历';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.recruit_numbers IS '招聘人数';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.publish_date IS '发布时间';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.workplace IS '上班地址';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.function_type IS '职能类别';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.keyword IS '关键字';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.job_info IS '职位信息';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.update_time IS '招聘更新时间';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.province IS '省份';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.city IS '城市';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.come_from IS '来源网站';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.url IS '来源网址';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.job_type IS '职位类型';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.job_msg IS '岗位福利等信息';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.job_welfare IS '岗位福利';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.main_points IS '招聘要点';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.update_at IS '更新时间';\n" +
            "COMMENT ON COLUMN ods_ly.ods_ly_recruit_info.min_id IS '最小ID';";
}
